import xlrd
import xlwt
from xlutils.copy import copy


class ExcelLoad():
    def __init__(self):
        self.xl_file = r"E:\Py_Project\http_interface_test\test_case\TestCase.xls"
        self.wb = xlrd.open_workbook(self.xl_file, formatting_info=True, encoding_override="gbk2312")
        self.sheet = self.wb.sheets()[0]
        self.cols = self.sheet.ncols  # 列
        self.rows = self.sheet.nrows  # 行
        self.search_value = "CheckUserPassWord"
        self.case_num_coord = self.search_value_return_value("用例编号")  # 用例编号坐标
        self.case_result_coord = self.search_value_return_value("结果")  # 用例结果坐标
        self.case_total_num = 0  # 用例总数

    def search_value_return_lst(self, value):
        '''
        查找相同值坐标，用于返回相同值的坐标列表
        :param value: 搜索值
        :return lst: 坐标列表
        '''
        lst = []
        for row in range(self.rows):
            for col in range(self.cols):
                if (self.sheet.cell(row, col).value == self.search_value):
                    lst.append((row, col))
        return lst

    def search_value_return_value(self, value):
        '''
        返回搜索值坐标
        :param value: 搜索值
        :return: 坐标
        '''
        for row in range(self.rows):
            for col in range(self.cols):
                if (self.sheet.cell(row, col).value == value):
                    return (row, col)

    # def get_key_list(self):
    #     '''
    #     根据单元格背景颜色查找需要获取的参数键值的行、列值
    #     xlx = self.sheet.cell_xf_index(row,col)
    #     xf = self.wb.xf_list[xlx]
    #     bgx = xf.background.pattern_colour_index
    #     不实用也不稳定
    #     :return: key_lst 键值
    #     '''
    #     key_lst = []
    #     for row in range(self.rows):
    #         for col in range(self.cols):
    #             xlx = self.sheet.cell_xf_index(row,col)
    #             xf = self.wb.xf_list[xlx]
    #             bgx = xf.background.pattern_colour_index
    #             # print(bgx,end=" ")
    #             if bgx == 29:
    #                 # key_lst.append(self.sheet.cell(row,col).value)
    #                 key_lst.append((row, col))
    #         # print("\n")
    #     return key_lst
    #     # print(self.sheet.cell(2,1).value)#excel表中的(3,B)

    def get_value_list(self, coord, num):
        '''
        获取指定列数的值列表
        :param coord:
        :param num:
        :return: 值列表
        '''
        row, col = coord
        list = []
        for i in range(num):
            list.append(self.sheet.cell(row + i, col).value)
        return list

    def get_case_steps_num(self, coord):
        '''
        查找测试用例数量，通过坐标向下搜索为float值认为是编号
        :return:num 用例数量
        '''
        num = 0
        x, y = coord
        while isinstance(self.sheet.cell(x + num + 1, y).value, float):
            num += 1
        return num

    def get_para_list(self):
        '''
        获取测试参数，构建成字典列表
        :return:
        '''
        start_id = self.case_num_coord[1]
        end_id = self.case_result_coord[1]
        col = self.case_num_coord[0] + 1
        lst = []
        self.case_total_num = self.get_case_steps_num(self.case_num_coord)  # 6
        for num in range(col, col + self.case_total_num):
            # print(col+self.case_total_num)
            dic = {}
            dic["num"] = self.sheet.cell(num, start_id).value
            # print(dic)
            for i in range(start_id, end_id)[1:]:
                dic[self.sheet.cell(col - 1, i).value] = self.sheet.cell(num, i).value
            lst.append(dic)
        return lst

    def write_result(self, res):
        '''
        :param res: 写入数据列表
        :return:
        写入结果，通过Num确定位置，写入reson、time、text
        '''
        wb = xlrd.open_workbook(self.xl_file, formatting_info=True, encoding_override="gbk2312")
        workbook = copy(wb)
        sheet = workbook.get_sheet(0)
        (row, col) = self.search_value_return_value(res["num"])
        sheet.write(row, col + 9, res["reason"])
        sheet.write(row, col + 10, res["response"])
        sheet.write(row, col + 11, res["time"])
        workbook.save(self.xl_file)


if __name__ == '__main__':
    myexcel = ExcelLoad()
    # value = (2,0)
    # print(myexcel.get_value_list(value,7))
    print(myexcel.get_para_list())
    # print(myexcel.get_value_num(value))
    # print(myexcel.search_value_return_first_value("用例编号"))
